﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbDepartment
    {
        /// <summary>
        /// Gets a department, based on the departmentId parameter.
        /// </summary>
        /// <param name="departmentId">Specifies the ID of the department to be retrieved.</param>
        /// <returns>Returns a Department object.</returns>
        public static Department GetDepartmentById(int departmentId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select d.departmentName, d.departmentAddress, d.departmentZipCode, d.departmentCity, d.departmentEmail, d.departmentPhone, d.departmentFax, d.departmentImage " +
                                   "from Department d " +
                                   "where d.departmentId = @DepartmentId";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@DepartmentId", departmentId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    Department dep = null;

                    while (dr.Read())
                    {
                        dep = new Department(departmentId);

                        dep.Name = dr["departmentName"].ToString();
                        dep.Address = dr["departmentAddress"].ToString();
                        dep.ZipCode = dr["departmentZipCode"].ToString();
                        dep.City = dr["departmentCity"].ToString();
                        dep.Email = dr["departmentEmail"].ToString();
                        dep.Phone = dr["departmentPhone"].ToString();
                        dep.ImageURL = dr["departmentImage"].ToString();

                        if (dr["departmentFax"] != null)
                            dep.Fax = dr["departmentFax"].ToString();
                    }

                    return dep;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all departments.
        /// </summary>
        /// <returns>Returns a list of Department objects.</returns>
        public static List<Department> GetAllDepartments()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Department> departments = new List<Department>();

                    string query = "select d.departmentId, d.departmentName, d.departmentAddress, d.departmentZipCode, d.departmentCity, d.departmentEmail, d.departmentPhone, d.departmentFax, d.departmentImage " +
                                   "from Department d " +
                                   "order by d.departmentName asc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Department dep = new Department(Convert.ToInt32(dr["departmentId"].ToString()));

                        dep.Name = dr["departmentName"].ToString();
                        dep.Address = dr["departmentAddress"].ToString();
                        dep.ZipCode = dr["departmentZipCode"].ToString();
                        dep.City = dr["departmentCity"].ToString();
                        dep.Email = dr["departmentEmail"].ToString();
                        dep.Phone = dr["departmentPhone"].ToString();
                        dep.ImageURL = dr["departmentImage"].ToString();

                        if (dr["departmentFax"] != null)
                            dep.Fax = dr["departmentFax"].ToString();

                        departments.Add(dep);
                    }

                    con.Close();
                    con.Dispose();

                    return departments;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates a department, based on the input parameters.
        /// </summary>
        /// <param name="name">Specifies the department name to be used.</param>
        /// <param name="hasImage">Specifies whether or not contact person has an image.</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>
        /// <param name="address">Specifies the address to be used.</param>
        /// <param name="zipCode">Specifies the zip code to be used.</param>
        /// <param name="city">Specifies the city to be used.</param>
        /// <param name="email">Specifies the email to be used in the contact person.</param>
        /// <param name="phone">Specifies the phone to be used in the contact person.</param>
        /// <param name="fax">Specifies the fax to be used.</param>
        public static void CreateDepartment(string name, bool hasImage, string imageFileName, string address, string zipCode, string city, string email, string phone, string fax)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "insert into Department (departmentName, departmentAddress, departmentZipCode, departmentCity, departmentEmail, departmentPhone, departmentFax, departmentImage) " +
                                   "values (@departmentName, @departmentAddress, @departmentZipCode, @departmentCity, @departmentEmail, @departmentPhone, @departmentFax, @departmentImage)";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@departmentName", name);

                    cmd.Parameters.AddWithValue("@departmentAddress", address);

                    cmd.Parameters.AddWithValue("@departmentZipCode", zipCode);

                    cmd.Parameters.AddWithValue("@departmentCity", city);

                    cmd.Parameters.AddWithValue("@departmentEmail", email);

                    cmd.Parameters.AddWithValue("@departmentPhone", phone);

                    cmd.Parameters.AddWithValue("@departmentFax", fax);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@departmentImage", "~/Images/Departments/" + name.Replace(" ", "") + "/" + imageFileName);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@departmentImage", "~/Images/Departments/defaultDep.jpg");
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a department, based on the departmentId parameter.
        /// </summary>
        /// <param name="departmentId">Specifies the ID of the department to be deleted.</param>
        public static void DeleteDepartment(int departmentId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "delete from Department " +
                                   "where departmentId = @departmentId ";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@departmentId", departmentId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Checks whether or not a department has an image, based on the departmentId parameter.
        /// </summary>
        /// <param name="departmentId">Specifies the ID of the department to be used.</param>
        /// <returns>Returns a boolean, specifying whether or not a department has an image.</returns>
        public static bool DepartmentHasImage(int departmentId)
        {
            try
            {
                bool result = false;
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select d.departmentImage " +
                                   "from Department d " +
                                   "where d.departmentId = @depID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@depID", departmentId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        if (dr["departmentImage"].ToString() != "~/Images/Departments/defaultDep.jpg")
                            result = true;
                    }

                    con.Close();
                    con.Dispose();

                    return result;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Edits a department, based on the departmentId parameter.
        /// </summary>
        /// <param name="departmentId">Specifies the ID of the department to be used.</param>
        /// <param name="name">Specifies the department name to be used.</param>
        /// <param name="hasImage">Specifies whether or not contact person has an image.</param>
        /// <param name="imagePath">Specifies the path to the image</param>
        /// <param name="imageFileName">Specifies the file name of the image to be used.</param>
        /// <param name="address">Specifies the address to be used.</param>
        /// <param name="zipCode">Specifies the zip code to be used.</param>
        /// <param name="city">Specifies the city to be used.</param>
        /// <param name="email">Specifies the email to be used in the contact person.</param>
        /// <param name="phone">Specifies the phone to be used in the contact person.</param>
        /// <param name="fax">Specifies the fax to be used.</param>
        public static void EditDepartment(int departmentId, string name, bool hasImage, string imagePath, string imageFileName, string address, string zipCode, string city, string email, string phone, string fax)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update Department " +
                                   "set departmentName = @departmentName, departmentAddress = @departmentAddress, departmentZipCode = @departmentZipCode, departmentCity = @departmentCity, departmentEmail = @departmentEmail, departmentPhone = @departmentPhone, departmentFax = @departmentFax ";

                    if (hasImage)
                    {
                        query += ", departmentImage = @departmentImage";
                    }

                    query += " where departmentId = @departmentId";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@departmentId", departmentId);

                    cmd.Parameters.AddWithValue("@departmentName", name);

                    cmd.Parameters.AddWithValue("@departmentAddress", address);

                    cmd.Parameters.AddWithValue("@departmentZipCode", zipCode);

                    cmd.Parameters.AddWithValue("@departmentCity", city);

                    cmd.Parameters.AddWithValue("@departmentEmail", email);

                    cmd.Parameters.AddWithValue("@departmentPhone", phone);

                    cmd.Parameters.AddWithValue("@departmentFax", fax);

                    if (hasImage)
                    {
                        cmd.Parameters.AddWithValue("@departmentImage", imagePath + "\\" + imageFileName);
                    }
                    else
                    {

                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Get a list of departments, based on the name parameter.
        /// </summary>
        /// <param name="name">Specifies the name to be used.</param>
        /// <returns>Returns a list of Department objects.</returns>
        public static List<Department> GetDepartmentByName(string name)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Department> departments = new List<Department>();

                    string query = "select d.departmentId, d.departmentName, d.departmentAddress, d.departmentZipCode, d.departmentCity, d.departmentEmail, d.departmentPhone, d.departmentFax, d.departmentImage " +
                                   "from Department d " +
                                   "where d.departmentName like '%' + @name + '%' " +
                                   "order by d.departmentName asc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@name", name);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Department dep = new Department(Convert.ToInt32(dr["departmentId"].ToString()));

                        dep.Name = dr["departmentName"].ToString();
                        dep.Address = dr["departmentAddress"].ToString();
                        dep.ZipCode = dr["departmentZipCode"].ToString();
                        dep.City = dr["departmentCity"].ToString();
                        dep.Email = dr["departmentEmail"].ToString();
                        dep.Phone = dr["departmentPhone"].ToString();
                        dep.Fax = dr["departmentFax"].ToString();
                        dep.ImageURL = dr["departmentImage"].ToString();

                        departments.Add(dep);
                    }

                    con.Close();
                    con.Dispose();

                    return departments;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }
    }
}